package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.CourseDTO;
import cn.edu.lingnan.pojo.Score;
import cn.edu.lingnan.pojo.ScoreDTO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ScoreDaoImp implements ScoreDao {
    @Override
    public List<ScoreDTO> ScorePageQuery(Integer page, Integer pageSize) {
        List<ScoreDTO> scoreList = new ArrayList<>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                    "root",
                    "123456"
            );
            // 建立会话

            String sql = "SELECT s.id, sc.cid, c.cname, sc.score, s.name AS sname, c.credit,sc.grade " +
                    "FROM school.score sc " +
                    "JOIN school.student s ON s.id = sc.sid " +
                    "JOIN school.course c ON sc.cid = c.cid " +
                    "LIMIT ?, ?";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, (page - 1) * pageSize);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ScoreDTO scoreDTO = new ScoreDTO();
                scoreDTO.setSid(rs.getInt("id"));
                scoreDTO.setCid(rs.getInt("cid"));
                scoreDTO.setCname(rs.getString("cname"));
                scoreDTO.setSname(rs.getString("sname"));
                scoreDTO.setScore(rs.getInt("score"));
                scoreDTO.setCredit(rs.getInt("credit"));
                scoreDTO.setGrade(rs.getInt("grade"));
                scoreList.add(scoreDTO);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return scoreList;
    }

    @Override
    public Integer getTotalScores() {
        Integer totalScores = 0;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM school.score");

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                totalScores = rs.getInt(1);
            }

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return totalScores;
    }

    @Override
    public List<ScoreDTO> getScoresByCid( Integer cid) {
        List<ScoreDTO> scoreDTOS = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            String sql= "SELECT s.id, sc.cid, c.cname, sc.score, s.name AS sname, c.credit ,sc.grade " +
                    "FROM school.score sc " +
                    "JOIN school.student s ON s.id = sc.sid " +
                    "JOIN school.course c ON sc.cid = c.cid " +
                    "WHERE CAST(sc.cid AS CHAR) LIKE ? " ;
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, "%" + cid + "%");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                ScoreDTO scoreDTO = new ScoreDTO();
                scoreDTO.setSid(rs.getInt("id"));
                scoreDTO.setCid(rs.getInt("cid"));
                scoreDTO.setCname(rs.getString("cname"));
                scoreDTO.setSname(rs.getString("sname"));
                scoreDTO.setScore(rs.getInt("score"));
                scoreDTO.setCredit(rs.getInt("credit"));
                scoreDTO.setGrade(rs.getInt("grade"));
                scoreDTOS.add(scoreDTO);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return scoreDTOS;
    }

    @Override
    public List<ScoreDTO> getCoursesBySid(Integer sid) {
        List<ScoreDTO> scoreDTOS = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            String sql= "SELECT c.ctime, t.tname, s.id, sc.cid, c.cname, sc.score, s.name AS sname, c.credit,sc.grade " +
                    "FROM school.score sc " +
                    "JOIN school.student s ON s.id = sc.sid " +
                    "JOIN school.course c ON sc.cid = c.cid " +
                    "JOIN school.teacher t ON c.tid = t.tid  " +
                    "WHERE  sc.sid = ? ";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, sid);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                ScoreDTO scoreDTO = new ScoreDTO();
                scoreDTO.setSid(rs.getInt("id"));
                scoreDTO.setCid(rs.getInt("cid"));
                scoreDTO.setCname(rs.getString("cname"));
                scoreDTO.setSname(rs.getString("sname"));
                scoreDTO.setCredit(rs.getInt("credit"));
                scoreDTO.setGrade(rs.getInt("grade"));
                scoreDTO.setTname(rs.getString("tname"));
                scoreDTO.setCtime(rs.getString("ctime"));
                scoreDTOS.add(scoreDTO);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return scoreDTOS;
    }

    @Override
    public List<ScoreDTO> getScoresBySid(Integer sid) {
        List<ScoreDTO> scoreDTOS = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            String sql= "SELECT c.ctime, t.tname, s.id, sc.cid, c.cname, sc.score, s.name AS sname, c.credit,sc.grade " +
                    "FROM school.score sc " +
                    "JOIN school.student s ON s.id = sc.sid " +
                    "JOIN school.course c ON sc.cid = c.cid " +
                    "JOIN school.teacher t ON c.tid = t.tid  " +
                    "WHERE  sc.sid = ? and score is not null ";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, sid);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                ScoreDTO scoreDTO = new ScoreDTO();
                scoreDTO.setSid(rs.getInt("id"));
                scoreDTO.setCid(rs.getInt("cid"));
                scoreDTO.setCname(rs.getString("cname"));
                scoreDTO.setSname(rs.getString("sname"));
                scoreDTO.setCredit(rs.getInt("credit"));
                scoreDTO.setScore(rs.getInt("score"));
                scoreDTO.setGrade(rs.getInt("grade"));
                scoreDTO.setTname(rs.getString("tname"));
                scoreDTO.setCtime(rs.getString("ctime"));
                scoreDTOS.add(scoreDTO);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return scoreDTOS;
    }

    @Override
    public List<ScoreDTO> getNoCoursesBySid(Integer sid) {
        List<ScoreDTO> scoreDTOS = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            String sql= "SELECT  c.cid, c.cname, c.credit, c.ctime, t.tname, t.tid, c.grade " +
                    "FROM school.course c LEFT JOIN school.teacher t ON c.tid = t.tid " +
                    "WHERE c.cid NOT IN ( " +
                    " SELECT s.cid " +
                    "FROM school.score s " +
                    " WHERE s.sid = ?)  ";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, sid);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                ScoreDTO scoreDTO = new ScoreDTO();
//                scoreDTO.setSid(rs.getInt("id"));
                scoreDTO.setCid(rs.getInt("cid"));
                scoreDTO.setCname(rs.getString("cname"));
//                scoreDTO.setSname(rs.getString("sname"));
                scoreDTO.setCredit(rs.getInt("credit"));
                scoreDTO.setGrade(rs.getInt("grade"));
                scoreDTO.setTname(rs.getString("tname"));
                scoreDTO.setCtime(rs.getString("ctime"));
                scoreDTOS.add(scoreDTO);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return scoreDTOS;
    }

    @Override
    public Integer addScore(Score score) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.score (sid, cid, grade) values(?,?,?) ");

          ps.setInt(1, score.getSid());
         ps.setInt(2, score.getCid());
         ps.setInt(3, score.getGrade());
            Integer count = ps.executeUpdate();
            return count;

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer deleteScore(Integer sid,Integer cid) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("delete from school.score where sid = ? and cid = ?");
            ps.setInt(1, sid);
            ps.setInt(2, cid);
            Integer count = ps.executeUpdate();
            return count;

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer countScore(Integer sid, Integer cid) {
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select count(*) from school.score where sid = ? and cid = ?");
            ps.setInt(1, sid);
            ps.setInt(2, cid);
            Integer count = ps.executeUpdate();
            return count;

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) {
        ScoreDao scoreDao = new ScoreDaoImp();
        Score score = new Score();
        score.setSid(16);
       score.setCid(601);
       score.setGrade(3);
        Integer integer = scoreDao.addScore(score);
        System.out.println("添加选课数"+integer);

    }
}
